package persistent

import (
	"context"
	"database/sql/driver"
	"fmt"
	"strings"
	"time"

	"gitee.com/leonscript/sally/v2/domain/aggregate"
	"gitee.com/leonscript/sally/v2/domain/entity"
	"gorm.io/gorm"
)

// StringSlice 自定义类型，实现 Scanner 和 Valuer 接口
// 用于处理逗号分隔字符串与字符串切片之间的转换
type StringSlice []string

// Scan 实现 sql.Scanner 接口，用于从数据库读取数据
func (s *StringSlice) Scan(value interface{}) error {
	if value == nil {
		*s = StringSlice{}
		return nil
	}

	switch v := value.(type) {
	case string:
		if v == "" {
			*s = StringSlice{}
		} else {
			parts := strings.Split(v, ",")
			for i := range parts {
				parts[i] = strings.TrimSpace(parts[i])
			}
			*s = StringSlice(parts)
		}
	case []byte:
		return s.Scan(string(v))
	default:
		return fmt.Errorf("cannot scan %T into StringSlice", value)
	}
	return nil
}

// Value 实现 driver.Valuer 接口，用于向数据库写入数据
func (s StringSlice) Value() (driver.Value, error) {
	if len(s) == 0 {
		return "", nil
	}
	return strings.Join(s, ","), nil
}

type ActivatedWorkflowClient struct {
	db *gorm.DB `name:"saas_db"`
}

func NewActivatedWorkflowClient(db *gorm.DB) *ActivatedWorkflowClient {
	return &ActivatedWorkflowClient{db: db}
}

// QueryActivatedWorkflows 查询激活的工作流列表
func (w *ActivatedWorkflowClient) QueryActivatedWorkflows(ctx context.Context, req aggregate.GetWorkflowsRequest) (result []ActivatedWorkflow, total int64, err error) {
	db := w.db.WithContext(ctx).
		Where("workflow.status != ?", entity.WorkflowArchiveStatus).
		Order("CASE WHEN workflow.status = -2 THEN 0 ELSE 1 END, workflow.created_at DESC")

	if len(req.IDs) > 0 {
		db = db.Where("workflow.id IN ?", req.IDs)
	}

	if req.SponsorID != "" {
		db = db.Where("sponsor_id = ?", req.SponsorID)
	}

	if req.BusinessCode != "" {
		db = db.Where("workflow.business_code = ?", req.BusinessCode)
	}

	if req.BusinessID != "" {
		db = db.Where("workflow.business_id = ?", req.BusinessID)
	}

	if req.FormContentSearch != "" {
		db = db.Where("JSON_SEARCH(workflow.form_content, 'one', ?) IS NOT NULL", "%"+req.FormContentSearch+"%")
	}

	if req.BusinessParams != nil {
		db = req.BusinessParams.WithGormCondition(db)
	}

	if req.WorkflowName != "" {
		db = db.Where("workflow_template.name LIKE ?", "%"+req.WorkflowName+"%")
	}

	if req.WorkflowStatus != 0 {
		db = db.Where("workflow.status = ?", req.WorkflowStatus)
	}
	if !req.CreatedAtStart.IsZero() {
		db = db.Where("workflow.created_at >= ?", req.CreatedAtStart)
	}

	if !req.CreatedAtEnd.IsZero() {
		db = db.Where("workflow.created_at <= ?", req.CreatedAtEnd)
	}

	if len(req.CurrentNodeReviewerIDs) > 0 {
		// 根据当前节点的审批人ID列表查询工作流
		// 通过 node_reviewer_task 表关联当前节点，筛选指定的审批人ID
		db = db.Where("workflow.current_node_id IN (SELECT DISTINCT node_id FROM node_reviewer_task WHERE reviewer_id IN ?)", req.CurrentNodeReviewerIDs)
	}

	// 查询某人已经审批过的工作流
	if len(req.ReviewedByUserIDs) > 0 {
		// 通过 node_reviewer_task 表查找已审批的任务，再关联到工作流
		db = db.Where(`workflow.id IN (
		SELECT DISTINCT w.id 
		FROM workflow w
		JOIN node n ON w.id = n.workflow_id
		JOIN node_reviewer_task nrt ON n.id = nrt.node_id
		WHERE nrt.reviewer_id IN ? AND nrt.status IN (?, ?)
	)`, req.ReviewedByUserIDs, entity.TaskPassedStatus, entity.TaskRejectedStatus)
	}

	// 使用 StringSlice 自定义类型，自动处理字符串与切片的转换
	err = db.
		Select(`workflow.id,workflow.status,workflow.form_content,workflow.created_at,workflow.updated_at,workflow.current_node_id,workflow.business_params,workflow.conclusion,workflow.sponsor_id,
        workflow_template.name,node_template.name AS current_node_name,node_template.id AS current_node_template_id,
        (SELECT GROUP_CONCAT(DISTINCT reviewer_id) FROM node_reviewer_task WHERE node_id = workflow.current_node_id) AS current_node_reviewer_ids`).
		Scopes(WorkflowView).
		Count(&total).Limit(req.PageSize).Offset((req.PageNo - 1) * req.PageSize).
		Find(&result).Error

	return
}

type ActivatedWorkflow struct {
	ID                     string      `gorm:"column:id;"`
	FormContent            string      `gorm:"form_content"`
	Name                   string      `gorm:"name"`
	CurrentNodeID          string      `gorm:"current_node_id"`
	CurrentNodeName        string      `gorm:"current_node_name"`
	CurrentNodeTemplateID  string      `gorm:"current_node_template_id"`
	CurrentNodeReviewerIDs StringSlice `gorm:"current_node_reviewer_ids"`
	Status                 int         `gorm:"status"`
	BusinessParams         string      `gorm:"business_params"`
	Conclusion             string      `gorm:"conclusion"`
	SponsorID              string      `gorm:"sponsor_id"`
	CreatedAt              time.Time   `gorm:"created_at"`
	UpdatedAt              time.Time   `gorm:"updated_at"`
}
